package dataGateway;

import java.sql.*;

import domainLogic.User;

/**
 * @author  jungmins
 */
public class UserTable {

	/**
	 * @uml.property  name="instance"
	 * @uml.associationEnd  
	 */
	private static UserTable instance = new UserTable();
	
	/**
	 * @return
	 * @uml.property  name="instance"
	 */
	public static UserTable getInstance()
	{
		return instance;
	}
	
	public UserTable() {  }

/*	
	private Connection getConnection() throws Exception {
		Connection conn = null;
	
		String driver = "org.apache.derby.jdbc.ClientDriver";
		String Url = "jdbc:derby://localhost:1527/auction";
		
		Class.forName(driver); 
		conn = DriverManager.getConnection(Url);
		return conn;
	} //getConnection()
*/	
	
	public boolean insertMember(User member) throws Exception {
		long startTime = System.currentTimeMillis();
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean ret = false;
		
		try {
			conn = ConnectionPool.getInstance().getConnection();
			String sql = "insert into users (username,name,password,email, " +
			    "phone, address, regdate, active) " +
			    "values(?,?,?,?,?,?,CURRENT_TIMESTAMP,'Y')";
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,member.getUsername()); 
			pstmt.setString(2,member.getName());
			pstmt.setString(3,member.getPasswd());
			pstmt.setString(4,member.getEmail());
			pstmt.setString(5,member.getPhone());
			pstmt.setString(6,member.getAddress());			
			if(pstmt.executeUpdate() >= 0)
				ret = true;;
			
		} catch(Exception ex) {
			ex.printStackTrace();
		} finally {
			if(pstmt != null) try { pstmt.close(); } catch(SQLException ex) { }		
			if(conn  != null) try {  conn.close(); } catch(SQLException ex) { }
		}
		System.out.println("insertMember: "+(System.currentTimeMillis()-startTime));
		
		return ret;
	} //insertMember

	public int userExist(String username) throws Exception {
		long startTime = System.currentTimeMillis();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		int cnt = 0;
		
		try {
			conn = ConnectionPool.getInstance().getConnection();
			String sql = "select count(username) cnt from users where username = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,username);
			
			rs = pstmt.executeQuery();
				if(rs.next()) {
					 cnt = rs.getInt("cnt");
				} 
			} catch(Exception ex) {
						ex.printStackTrace();
			} finally {
				if(rs    != null) try {    rs.close(); } catch(SQLException ex) { }		
				if(pstmt != null) try { pstmt.close(); } catch(SQLException ex) { }		
				if(conn  != null) try {  conn.close(); } catch(SQLException ex) { }
			}
		System.out.println("userExist: "+(System.currentTimeMillis()-startTime));

		return cnt;
		}  //userExist()
	
	public int userCheck(String username, String password) throws Exception {
		long startTime = System.currentTimeMillis();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		String dbpasswd = "";
		int x = -1;
		
		try {
			conn = ConnectionPool.getInstance().getConnection();			
			System.out.println("userCheck(Conn): "+(System.currentTimeMillis()-startTime));
			String sql = "select password from users where username = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,username);
			
			rs = pstmt.executeQuery();
			
			System.out.println("userCheck(query): "+(System.currentTimeMillis()-startTime));
			
			if(rs.next()) {
				dbpasswd = rs.getString("password");
				if(dbpasswd.equals(password))
					x = 1;
				else 
					x = 0; //Wrong passwd
			} else 
				x = -1;  //No ID
		} catch(Exception ex) {
			ex.printStackTrace();
		} finally {
			if(rs    != null) try {    rs.close(); } catch(SQLException ex) { }		
			if(pstmt != null) try { pstmt.close(); } catch(SQLException ex) { }		
			if(conn  != null) try {  conn.close(); } catch(SQLException ex) { }
		}
		
		System.out.println("userCheck: "+(System.currentTimeMillis()-startTime));
		return x;
	}  //userCheck()

} //class
